﻿//
// (C) Copyright 2009 Irantha Suwandarathna (irantha@gmail.com)
// All rights reserved.
//

using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using System.IO;
using System.Diagnostics;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using EffiProzDB;
using EffiProzDB.Lib;
using EffiProzDB.DataTypes;
using EffiProzDB.Persist;

namespace EffiProzDBTest
{
    /// <summary>
    /// Summary description for TestCacheSize
    /// </summary>
    [TestClass]
    public class TestCacheSize : BaseTest
    {

        bool filedb = true;
        bool shutdown = true;

        // type of the big table {MEMORY | CACHED | TEXT | ""}
        String tableType = "CACHED";
        int cacheScale = 14;
        int cacheSizeScale = 10;
        bool nioMode = false;

        // script format {TEXT | BINARY | COMPRESSED}
        String logType = "BINARY";
        //int writeDelay = 60;
        bool indexZip = false;
        bool indexLastName = false;
        bool addForeignKey = false;
        bool refIntegrity = true;

        // may speed up inserts when tableType=="CACHED"
        bool createTempTable = false;

        // introduces fragmentation to the .data file during insert
        bool deleteWhileInsert = false;
        int deleteWhileInsertInterval = 10000;

        // size of the tables used in test
        int bigrows = 100;

        // number of ops
       // int bigops = 20;
        int smallops = 10;
        int smallrows = 0xf;

        // if the extra table needs to be created and filled up
        bool multikeytable = false;

        //
        String filler = "ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ"
                        + "ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ";


        StreamWriter writer;
        string filepath;

        public TestCacheSize()
        {
            filepath = dataPath;
        }

        private TestContext testContextInstance;

        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }

        protected void setUp()
        {

            CleanUp("CacheSizeTest");
            sessionProxy = DatabaseManager.newSession(DatabaseURL.S_FILE, dataPath + "CacheSizeTest",
                   user, password, props);

            try
            {
                writer = new StreamWriter(new FileStream("speedtests.html", FileMode.OpenOrCreate, FileAccess.Write));

                writer.Write("<table>\n");
                storeResult(DateTime.Now.ToString(), 0, 0, 0);
                storeResult(filepath + " " + tableType + " " + nioMode,
                            cacheScale, 0, 0);
            }
            catch (Exception ) { }

            try
            {
                if (filedb)
                {
                    TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "SET WRITE_DELAY " + 2);
                    TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "SET CHECKPOINT DEFRAG " + 0);
                    TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "SET SCRIPTFORMAT " + logType);
                    TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "SET LOGSIZE " + 0);
                    TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "SET PROPERTY \"hsqldb.applog\" " + 1);
                    TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "SET PROPERTY \"hsqldb.cache_scale\" "
                                       + cacheScale);
                    TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "SET PROPERTY \"hsqldb.cache_size_scale\" "
                                       + cacheSizeScale);
                    TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "SET PROPERTY \"hsqldb.nio_data_file\" "
                                       + nioMode);
                    TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "SHUTDOWN");
                    sessionProxy.close();

                }
            }
            catch (Exception e)
            {
                Assert.Fail(e.StackTrace);

            }
        }


        /**
   * Fill up the cache
   *
   *
   */
        public void testFillUp()
        {

            Stopwatch sw = new Stopwatch();
            String ddl1 = "DROP TABLE test IF EXISTS";
            //String ddl11 = "DROP TABLE zip IF EXISTS";
            String ddl2 = "CREATE TABLE zip( zip INT IDENTITY )";
            String ddl3 = "CREATE " + tableType + " TABLE test( id INT IDENTITY,"
                          + " firstname VARCHAR(20), " + " lastname VARCHAR(20), "
                          + " zip INTEGER, " + " filler VARCHAR(300))";
            String ddl31 = "SET TABLE test SOURCE \"test.csv;cache_scale="
                           + cacheScale + "\"";

            // adding extra index will slow down inserts a bit
            String ddl4 = "CREATE INDEX idx1 ON TEST (lastname)";

            // adding this index will slow down  inserts a lot
            String ddl5 = "CREATE INDEX idx2 ON TEST (zip)";

            // referential integrity checks will slow down inserts a bit
            String ddl6 =
                "ALTER TABLE test add constraint c1 FOREIGN KEY (zip) REFERENCES zip(zip) ON DELETE CASCADE;";
            String ddl7 = "CREATE TEMP TABLE temptest( id INT,"
                          + " firstname VARCHAR, " + " lastname VARCHAR, "
                          + " zip INTEGER, " + " filler VARCHAR)";
            String mddl1 = "DROP TABLE test2 IF EXISTS";
            String mddl2 = "CREATE " + tableType
                           + " TABLE test2( id1 INT, id2 INT,"
                           + " firstname VARCHAR, " + " lastname VARCHAR, "
                           + " zip INTEGER, " + " filler VARCHAR, "
                           + " PRIMARY KEY (id1,id2) )";
            String mdd13 = "SET TABLE test2 SOURCE \"test2.csv;cache_scale="
                           + cacheScale + "\"";

            try
            {

                //             System.Diagnostics.Debug.WriteLine("Connecting");
                sw.Reset();
                sw.Start();

                sessionProxy = DatabaseManager.newSession(DatabaseURL.S_FILE, dataPath + "CacheSizeTest",
                       user, password, props);

                System.Diagnostics.Debug.WriteLine("connection time -- " + sw.ElapsedMilliseconds);
                sw.Reset(); sw.Start();


                Random randomgen = new Random();

                //            TestHelper.ExecuteUpdateSQL_Success(sessionProxy,"SET WRITE_DELAY " + writeDelay);
                TestHelper.ExecuteUpdateSQL_Success(sessionProxy, ddl1);
                TestHelper.ExecuteUpdateSQL_Success(sessionProxy, ddl2);
                TestHelper.ExecuteUpdateSQL_Success(sessionProxy, ddl3);

                if (tableType.Equals("TEXT"))
                {
                    TestHelper.ExecuteUpdateSQL_Success(sessionProxy, ddl31);
                }

                //             System.Diagnostics.Debug.WriteLine("test table with no index");
                if (indexLastName)
                {
                    TestHelper.ExecuteUpdateSQL_Success(sessionProxy, ddl4);
                    System.Diagnostics.Debug.WriteLine("created index on lastname");
                }

                if (indexZip)
                {
                    TestHelper.ExecuteUpdateSQL_Success(sessionProxy, ddl5);
                    System.Diagnostics.Debug.WriteLine("created index on zip");
                }

                if (addForeignKey)
                {
                    TestHelper.ExecuteUpdateSQL_Success(sessionProxy, ddl6);
                    System.Diagnostics.Debug.WriteLine("added foreign key");
                }

                if (createTempTable)
                {
                    TestHelper.ExecuteUpdateSQL_Success(sessionProxy, ddl7);
                    System.Diagnostics.Debug.WriteLine("created temp table");
                }

                if (multikeytable)
                {
                    TestHelper.ExecuteUpdateSQL_Success(sessionProxy, mddl1);
                    TestHelper.ExecuteUpdateSQL_Success(sessionProxy, mddl2);

                    if (tableType.Equals("TEXT"))
                    {
                        TestHelper.ExecuteUpdateSQL_Success(sessionProxy, mdd13);
                    }

                    System.Diagnostics.Debug.WriteLine("created multi key table");
                }

                //            TestHelper.ExecuteUpdateSQL_Success(sessionProxy,"CREATE INDEX idx3 ON tempTEST (zip);");
                System.Diagnostics.Debug.WriteLine("complete setup time -- " + sw.ElapsedMilliseconds
                                  + " ms");
                fillUpBigTable(filler, randomgen);

                if (multikeytable)
                {
                    fillUpMultiTable(filler, randomgen);
                }

                sw.Reset(); sw.Start();

                if (shutdown)
                {
                    TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "SHUTDOWN");

                    long time = sw.ElapsedMilliseconds;

                    storeResult("shutdown", 0, time, 0);
                    System.Diagnostics.Debug.WriteLine("shutdown time  -- " + time + " ms");
                }

                sessionProxy.close();
            }
            catch (Exception e)
            {
                System.Diagnostics.Debug.WriteLine(e.Message);
                Assert.Fail(e.Message);
            }
        }

        private void fillUpBigTable(String filler,
                               Random randomgen)
        {

            Stopwatch sw = new Stopwatch();
            int i;
            Result ps =
                TestHelper.GetPrepareStatement(sessionProxy, "INSERT INTO zip VALUES(?)");

            object[] vals = new object[1];
            for (i = 0; i <= smallrows; i++)
            {
                vals[0] = i;
                TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, vals);
            }


            TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "SET REFERENTIAL_INTEGRITY " + this.refIntegrity);

            ps = TestHelper.GetPrepareStatement(sessionProxy,
                 "INSERT INTO test (firstname,lastname,zip,filler) VALUES (?,?,?,?)");

            vals = new object[4];
            vals[0] = "Julia";
            vals[1] = "Clancy";

            for (i = 0; i < bigrows; i++)
            {
                vals[2] = nextIntRandom(randomgen, smallrows);

                {
                    // small rows
                    long nextrandom = randomgen.Next();
                    int randomlength = (int)nextrandom & 0x7f;

                    if (randomlength > filler.Length)
                    {
                        randomlength = filler.Length;
                    }

                    String varfiller = filler.Substring(0, randomlength);

                    vals[3] = nextrandom + varfiller;
                }

                /*
                            {
                                // big rows
                                long nextrandom   = randomgen.nextLong();
                                int  randomlength = (int) nextrandom & 0x7ff;

                                if (randomlength > filler.length() * 20) {
                                    randomlength = filler.length() * 20;
                                }

                                StringBuffer sb = new StringBuffer(0xff);

                                for (int j = 0; j < 20; j++) {
                                    sb.append(filler);
                                }

                                String varfiller = sb.substring(0, randomlength);

                                ps.setString(4, nextrandom + varfiller);
                            }
                */
                TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, vals);

                //if (reportProgress && (i + 1) % 10000 == 0) {
                //     System.Diagnostics.Debug.WriteLine("insert " + (i + 1) + " : "
                //                       + sw.elapsedTime());
                //}

                // delete and add 4000 rows to introduce fragmentation
                if (deleteWhileInsert && i != 0
                        && i % deleteWhileInsertInterval == 0)
                {
                    Result res = TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "CALL IDENTITY();");

                    Iterator rs = res.iterator();

                    object[] d = (object[])rs.next();

                    int lastId = (int)d[0];

                    TestHelper.ExecuteUpdateSQL_Success(sessionProxy,
                        "SELECT * INTO TEMP tempt FROM test WHERE id > "
                        + (lastId - 4000));
                    TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "DELETE FROM test WHERE id > "
                                       + (lastId - 4000));
                    TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "INSERT INTO test SELECT * FROM tempt");
                    TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "DROP TABLE tempt");
                }
            }



            //            TestHelper.ExecuteUpdateSQL_Success(sessionProxy,"INSERT INTO test SELECT * FROM temptest;");
            //            TestHelper.ExecuteUpdateSQL_Success(sessionProxy,"DROP TABLE temptest;");
            //            TestHelper.ExecuteUpdateSQL_Success(sessionProxy,ddl7);
            long time = sw.ElapsedMilliseconds;
            long rate = ((long)i * 1000) / (time + 1);

            storeResult("insert", i, time, rate);
            System.Diagnostics.Debug.WriteLine("insert time for " + i + " rows -- " + time
                              + " ms -- " + rate + " tps");
        }

        private void fillUpMultiTable(String filler,
                                      Random randomgen)
        {

            Stopwatch sw = new Stopwatch();
            int i;


            Result ps = TestHelper.GetPrepareStatement(sessionProxy,
                "INSERT INTO test2 (id1, id2, firstname,lastname,zip,filler) VALUES (?,?,?,?,?,?)");

            object[] vals = new object[6];
            vals[2] = "Julia";
            vals[3] = "Clancy";


            int id1 = 0;

            for (i = 0; i < bigrows; i++)
            {
                int id2 = nextIntRandom(randomgen, int.MaxValue);

                if (i % 1000 == 0)
                {
                    id1 = nextIntRandom(randomgen, int.MaxValue);
                }

                vals[0] = id1;
                vals[1] = id2;
                vals[4] = nextIntRandom(randomgen, smallrows);

                long nextrandom = randomgen.Next();
                int randomlength = (int)nextrandom & 0x7f;

                if (randomlength > filler.Length)
                {
                    randomlength = filler.Length;
                }

                String varfiller = filler.Substring(0, randomlength);

                vals[5] = nextrandom + varfiller;

                try
                {
                    TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, vals);
                }
                catch (Exception e)
                {
                    Assert.Fail(e.StackTrace);
                }

                //if (reportProgress && (i + 1) % 10000 == 0) {
                //     System.Diagnostics.Debug.WriteLine("insert " + (i + 1) + " : "
                //                       + sw.elapsedTime());
                //}
            }

            sessionProxy.close();
            System.Diagnostics.Debug.WriteLine("total multi key rows inserted: " + i);
            System.Diagnostics.Debug.WriteLine("insert time: " + sw.ElapsedMilliseconds + " rps: "
                              + (i * 1000 / (sw.ElapsedMilliseconds + 1)));
        }

        protected void checkResults()
        {

            try
            {
                Stopwatch sw = new Stopwatch();      

                sessionProxy = DatabaseManager.newSession(DatabaseURL.S_FILE, dataPath + "CacheSizeTest",
                     user, password, props);

                long time = sw.ElapsedMilliseconds;

                storeResult("reopen", 0, time, 0);
                System.Diagnostics.Debug.WriteLine("database reopen time -- " + time + " ms");
                sw.Reset(); sw.Start();



                //            sStatement.execute("SET WRITE_DELAY " + writeDelay);
                checkSelects();
                checkUpdates();

                sw.Reset(); sw.Start();

                if (shutdown)
                {
                    TestHelper.ExecuteUpdateSQL_Success(sessionProxy, "SHUTDOWN");

                    time = sw.ElapsedMilliseconds;

                    storeResult("shutdown", 0, time, 0);
                    System.Diagnostics.Debug.WriteLine("shutdown time  -- " + time + " ms");
                }

                sessionProxy.close();

                //            System.out.println("database close time  -- " + sw.elapsedTime() + " ms");
            }
            catch (Exception e)
            {
                Assert.Fail(e.Message);
            }
        }

        private void checkSelects()
        {

            countTestID();
            selectID();
            selectZipTable();
        }

        private void checkUpdates()
        {

            //        updateIDLinear();
            //        updateID();
            updateTestString();
            countTestID();
            deleteTest();
            countTestID();
            countZip();
        }

        private void countZip() {

        try {
            Stopwatch sw = new Stopwatch();

           Result rs = TestHelper.ExecuteSelectSQL(sessionProxy, "SELECT count(*) from zip where zip > -1", null);

            

            //rs.next();
            //System.out.println("count time (zip table) " + rs.getInt(1)
            //                   + " rows  -- " + sw.elapsedTime() + " ms");
        } catch (Exception ) {}
    }


        void deleteTest()
        {

            Stopwatch sw = new Stopwatch();
            Random randomgen = new Random();
            int i = 0;
            //bool slow = false;

            int count = 0;
            int random = 0;


            try
            {
                Result ps = TestHelper.GetPrepareStatement(sessionProxy,
                         "DELETE FROM test WHERE id = ?");



                for (i = 0; i < smallops; i++)
                {
                    random = nextIntRandom(randomgen, bigrows);

                    //                random = i;


                    TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, new object[] { random });

                    /*
                                    if ((i + 1) % 10000 == 0) {
                                        Statement st = cConnection.createStatement();

                                        st.execute("CHECKPOINT DEFRAG");
                                        st.close();
                                    }
                    //*/
                    //                if (reportProgress && (i + 1) % 10000 == 0
                    //                        || (slow && (i + 1) % 100 == 0)) {
                    //                    System.out.println("delete " + (i + 1) + " : "
                    //                                       + sw.elapsedTime() + " rps: "
                    //                                       + (i * 1000 / (sw.elapsedTime() + 1)));
                    //                }
                }


            }
            catch (Exception e)
            {
                System.Diagnostics.Debug.WriteLine("error : " + random + " " + e.Message);
            }


            long time = sw.ElapsedMilliseconds;
            long rate = ((long)i * 1000) / (time + 1);

            storeResult("delete with random id", count, time, rate);
            System.Diagnostics.Debug.WriteLine("delete time for random id " + count + " rows  -- "
                           + time + " ms -- " + rate + " tps");
        }



        void updateTestString()
        {

            Stopwatch sw = new Stopwatch();
            Random randomgen = new Random();
            int i = 0;
            bool slow = false;

            int random = 0;

            try
            {

                Result ps = TestHelper.GetPrepareStatement(sessionProxy,
                       "UPDATE test SET filler = ? WHERE id = ? and zip <> "
                   + smallrows);

                for (i = 0; i < smallops * 2; i++)
                {
                    random = nextIntRandom(randomgen, bigrows - 1);

                    int randomLength = nextIntRandom(randomgen, filler.Length);
                    String newFiller = filler.Substring(randomLength);



                    TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, new object[] { newFiller, random });


                    if ((i + 1) % 10000 == 0
                            || (slow && (i + 1) % 100 == 0))
                    {
                        System.Diagnostics.Debug.WriteLine("Update " + (i + 1) + " : "
                                          + sw.ElapsedMilliseconds + " rps: "
                                          + (i * 1000 / (sw.ElapsedMilliseconds + 1)));
                    }
                }


            }
            catch (Exception e)
            {
                System.Diagnostics.Debug.WriteLine("error : " + random + " " + e.Message);
            }


            long time = sw.ElapsedMilliseconds;
            long rate = ((long)i * 1000) / (time + 1);

            storeResult("update with random id", i, time, rate);
            System.Diagnostics.Debug.WriteLine("update time with random id " + i + " rows  -- "
                           + time + " ms -- " + rate + " tps");
        }

        void selectZipTable()
        {


            Stopwatch sw = new Stopwatch();
            Random randomgen = new Random();
            int i = 0;
            bool slow = false;

            try
            {
                Result ps = TestHelper.GetPrepareStatement(sessionProxy,
                    "SELECT zip FROM zip WHERE zip = ?");

                for (i = 0; i < smallops; i++)
                {
                    TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, new object[] { nextIntRandom(randomgen, smallrows - 1) });

                    if ((i + 1) % 10000 == 0
                            || (slow && (i + 1) % 100 == 0))
                    {
                        System.Diagnostics.Debug.WriteLine("Select " + (i + 1) + " : "
                                            + (sw.ElapsedMilliseconds + 1));
                    }
                }


            }
            catch (Exception e)
            {
                System.Diagnostics.Debug.WriteLine(e.Message);
            }



            long time = sw.ElapsedMilliseconds;
            long rate = ((long)i * 1000) / (time + 1);

            storeResult("select random id", i, time, rate);
            System.Diagnostics.Debug.WriteLine("select time for random zip from zip table " + i
                           + " rows  -- " + time + " ms -- " + rate + " tps");
        }


        void selectID()
        {

            Stopwatch sw = new Stopwatch();
            Random randomgen = new Random();
            int i = 0;
            bool slow = false;

            try
            {
                Result ps = TestHelper.GetPrepareStatement(sessionProxy,
                    "SELECT firstname,lastname,zip,filler FROM test WHERE id = ?");

                for (i = 0; i < smallops; i++)
                {
                    TestHelper.ExecuteGetPrepareStatement(sessionProxy, ps, new object[] { nextIntRandom(randomgen, bigrows - 1) });

                    if ((i + 1) % 10000 == 0
                            || (slow && (i + 1) % 100 == 0))
                    {
                        System.Diagnostics.Debug.WriteLine("Select " + (i + 1) + " : "
                                            + (sw.ElapsedMilliseconds + 1));
                    }
                }


            }
            catch (Exception e)
            {
                System.Diagnostics.Debug.WriteLine(e.Message);
            }

            long time = sw.ElapsedMilliseconds;
            long rate = ((long)i * 1000) / (time + 1);

            storeResult("select random id", i, time, rate);
            System.Diagnostics.Debug.WriteLine("select time for random id " + i + " rows  -- "
                               + time + " ms -- " + rate + " tps");
        }


        private void countTestID()
        {

            try
            {
                Stopwatch sw = new Stopwatch();

                // the tests use different indexes
                // use primary index
                Result result = TestHelper.ExecuteSelectSQL(sessionProxy, "SELECT count(*) from TEST where id > -1", 1);

                Iterator rs = result.iterator();

                object[] vals = (object[])rs.next();

                long time = sw.ElapsedMilliseconds;
                long rate = ((long)bigrows * 1000) / (time + 1);

                storeResult("count (index on id)", (int)vals[0], time, rate);
                System.Diagnostics.Debug.WriteLine("count time (index on id) " + (int)vals[0]
                                   + " rows  -- " + time + " ms -- " + rate
                                   + " tps");
            }
            catch (Exception ) { }
        }


        void storeResult(String description, int count, long time, long rate)
        {

            try
            {
                writer.Write("<tr><td>" + description + "</td><td>" + count
                             + "</td><td>" + time + "</td><td>" + rate
                             + "</td></tr>\n");
            }
            catch (Exception) { }
        }

        int nextIntRandom(Random r, int range)
        {

            int b = Math.Abs(r.Next());
            return b % range;
        }


        #region Additional test attributes
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // Use ClassCleanup to run code after all tests in a class have run
        // [ClassCleanup()]
        // public static void MyClassCleanup() { }
        //
        // Use TestInitialize to run code before running each test 
        // [TestInitialize()]
        // public void MyTestInitialize() { }
        //
        // Use TestCleanup to run code after each test has run
        // [TestCleanup()]
        // public void MyTestCleanup() { }
        //
        #endregion

        [TestMethod]
        public void TestCachSize()
        {
            setUp();
            testFillUp();
            checkResults();
        }
    }
}
